﻿namespace QLPhongKham.Access.Duoc
{
    using ConfigDatabase.Class;
    using Npgsql;
    using QLPhongKham.Access;
    using QLPhongKham.BaseClass.Chung;
    using QLPhongKham.BaseClass.Duoc;
    using System;
    using System.Data;

    public class DPhieuDatHang
    {
        private ConnectDatabase _condata;
        private NpgsqlConnection _conn;
        private General _xuli;

        public DPhieuDatHang()
        {
            this._condata = new ConnectDatabase();
            this._xuli = new General(this._condata);
        }

        public DPhieuDatHang(ConnectDatabase condata)
        {
            this._condata = condata;
            this._xuli = new General(this._condata);
        }

        public long f_get_capsophieu()
        {
            long num = 0L;
            try
            {
                num = long.Parse(this._xuli.f_Get_dulieu("select max(" + dtoDPhieuDatHang.columnSOPHIEU + ") as sophieu  from " + this._xuli.pSchema + "." + dtoDPhieuDatHang.tableName + " where to_char(" + dtoDPhieuDatHang.columnNGAY + ",'yyyy')='" + DateTime.Now.ToString("yyyy") + "'").Tables[0].Rows[0][0].ToString());
            }
            catch
            {
            }
            return (num + 1L);
        }

        public bool f_huyphieunhap(long id)
        {
            int num = -1;
            try
            {
                NpgsqlConnection connect = new NpgsqlConnection(this._condata.StringConnect);
                connect.Open();
                NpgsqlTransaction transaction = connect.BeginTransaction();
                try
                {
                    num = this._xuli.f_Update_dulieu(string.Concat(new object[] { "delete from ", this._xuli.pSchema, ".", dtoDPhieuDatHangCT.tableName, " where ", dtoDPhieuDatHangCT.columnIDPHIEU, "=", id }), connect);
                    num = -1;
                    num = this._xuli.f_Update_dulieu(string.Concat(new object[] { "delete from ", this._xuli.pSchema, ".", dtoDPhieuDatHang.tableName, " where ", dtoDPhieuDatHang.columnID, "=", id }), connect);
                    if (num > -1)
                    {
                        transaction.Commit();
                    }
                    else
                    {
                        transaction.Rollback();
                    }
                }
                catch
                {
                    transaction.Rollback();
                }
                finally
                {
                    connect.Close();
                    connect.Dispose();
                }
            }
            catch
            {
            }
            return (num > -1);
        }

        public long f_insert_phieudathang(dtoDPhieuDatHang dathang, dtoDPhieuDatHangCT[] chitiet)
        {
            long num2;
            this._conn = new NpgsqlConnection(this._condata.StringConnect);
            this._conn.Open();
            try
            {
                dathang.pID = long.Parse(this._xuli.f_Get_CapID());
                if (!dathang.Insert(this._conn))
                {
                    return 0L;
                }
                NpgsqlTransaction transaction = this._conn.BeginTransaction();
                int index = 0;
                index = 0;
                while (index < chitiet.Length)
                {
                    chitiet[index].pIDPHIEU = dathang.pID;
                    if (chitiet[index].pID != 0L)
                    {
                        if (!chitiet[index].Update(this._conn))
                        {
                            transaction.Rollback();
                            break;
                        }
                    }
                    else
                    {
                        chitiet[index].pID = long.Parse(this._xuli.f_Get_CapID());
                        if (!chitiet[index].Insert(this._conn))
                        {
                            chitiet[index].pID = 0L;
                            transaction.Rollback();
                            break;
                        }
                    }
                    index++;
                }
                if (index == chitiet.Length)
                {
                    transaction.Commit();
                    return dathang.pID;
                }
                dathang.Delete();
                num2 = 0L;
            }
            catch
            {
                num2 = 0L;
            }
            finally
            {
                this._conn.Close();
                this._conn = null;
            }
            return num2;
        }

        public DataTable f_loaddata_dathang(long id, string tungay, string denngay)
        {
            DataTable table = new DataTable();
            string str = this._condata.Schema + ".";
            string sql = "select a." + dtoDPhieuDatHang.columnID + " as id,a." + dtoDPhieuDatHang.columnSOPHIEU + " as sophieu,to_char(" + dtoDPhieuDatHang.columnNGAY + ",'dd/mm/yyyy hh24:mi') as ngay,to_char(" + dtoDPhieuDatHang.columnNGAYGIAO + ",'dd/mm/yyyy hh24:mi') as ngaygiao,a." + dtoDPhieuDatHang.columnIDDANGNHAP + " as iddangnhap,a." + dtoDPhieuDatHang.columnIDNHACUNGCAP + " as idnhacc,b2." + dtoDDMNhaCungCap.columnTEN + " as tennhacc,b2." + dtoDDMNhaCungCap.columnDIACHI + " as diachi,b2." + dtoDDMNhaCungCap.columnDIENTHOAI + " as dienthoai,b2." + dtoDDMNhaCungCap.columnDAIDIEN + " as nguoidaidien,b." + dtoDMDangNhap.columnTENNGUOIDUNG + " as tennguoidangnhap,a." + dtoDPhieuDatHang.columnGHICHU + " as ghichu,a." + dtoDPhieuDatHang.columnPHUONGTHUCTHANHTOAN + " as ptthanhtoan,a." + dtoDPhieuDatHang.columnPHUONGTHUCGIAOHANG + " as ptgiaohang from " + str + dtoDPhieuDatHang.tableName + " a left join " + str + dtoDMDangNhap.tableName + " b on a." + dtoDPhieuDatHang.columnIDDANGNHAP + "=b." + dtoDMDangNhap.columnID + " left join " + str + dtoDDMNhaCungCap.tableName + " b2 on a." + dtoDPhieuDatHang.columnIDNHACUNGCAP + "=b2." + dtoDDMNhaCungCap.columnID + " where 1=1 " + ((id == 0L) ? "" : string.Concat(new object[] { " and a.", dtoDPhieuDatHang.columnID, "=", id })) + ((tungay == "") ? "" : (" and to_timestamp(to_char(a." + dtoDPhieuDatHang.columnNGAY + ",'dd/mm/yyyy'),'dd/mm/yyyy')>=to_timestamp('" + tungay + "','dd/mm/yyyy')")) + ((denngay == "") ? "" : (" and to_timestamp(to_char(a." + dtoDPhieuDatHang.columnNGAY + ",'dd/mm/yyyy'),'dd/mm/yyyy')<=to_timestamp('" + denngay + "','dd/mm/yyyy')")) + " order by a." + dtoDPhieuDatHang.columnNGAY + " desc";
            return this._xuli.f_Get_dulieu(sql).Tables[0];
        }

        public DataTable f_loaddata_dathangct(long idphieudathang)
        {
            DataTable table = new DataTable();
            string str = this._condata.Schema + ".";
            string sql = string.Concat(new object[] { 
                "select b.", dtoDPhieuDatHangCT.columnIDPHIEU, " as idphieu,b.", dtoDPhieuDatHangCT.columnID, " as id,b2.", dtoDDMDuocPham.columnID, " as iddp,b2.", dtoDDMDuocPham.columnTEN, " as tendp,b2.", dtoDDMDuocPham.columnMA, " as madp,b2.", dtoDDMDuocPham.columnDVT, " as dvt,b2.", dtoDDMDuocPham.columnTENHOATCHAT, " as tenhoatchat,b.", dtoDPhieuDatHangCT.columnSOLUONGTONG, 
                " as soluongtong,b.", dtoDPhieuDatHangCT.columnSOLUONG, " as soluong,b.", dtoDPhieuDatHangCT.columnIDQUYDOI, " as idquydoi,b.", dtoDPhieuDatHangCT.columnDONGIA, " as dongia,b.", dtoDPhieuDatHangCT.columnSOLUONG, "*b.", dtoDPhieuDatHangCT.columnDONGIA, " as thanhtien,b.", dtoDPhieuDatHangCT.columnSOLUONGDAGIAO, " as soluonggiao,b.", dtoDPhieuDatHangCT.columnIDDANGNHAP, " as iddangnhap,b.", dtoDPhieuDatHangCT.columnHANGSX, 
                " as hangsx,case when b3.", dtoDDMQuyDoi.columnID, " is null then b2.", dtoDDMDuocPham.columnDVT, " else b3.", dtoDDMQuyDoi.columnTEN, " end as tenquydoi from ", str, dtoDPhieuDatHang.tableName, " a inner join ", str, dtoDPhieuDatHangCT.tableName, " b on a.", dtoDPhieuDatHang.columnID, "=b.", dtoDPhieuDatHangCT.columnIDPHIEU, 
                " inner join ", str, dtoDDMDuocPham.tableName, " b2 on b.", dtoDPhieuDatHangCT.columnIDDUOCPHAM, "=b2.", dtoDDMDuocPham.columnID, " left join ", str, dtoDDMQuyDoi.tableName, " b3 on b.", dtoDPhieuDatHangCT.columnIDQUYDOI, "=b3.", dtoDDMQuyDoi.columnID, " where a.", dtoDPhieuDatHang.columnID, 
                "=", idphieudathang, " order by a.", dtoDPhieuDatHang.columnNGAY, " desc"
             });
            return this._xuli.f_Get_dulieu(sql).Tables[0];
        }

        public long f_update_dathang(dtoDPhieuDatHang dathang, dtoDPhieuDatHangCT[] chitiet)
        {
            long num2;
            this._conn = new NpgsqlConnection(this._condata.StringConnect);
            this._conn.Open();
            NpgsqlTransaction transaction = this._conn.BeginTransaction();
            try
            {
                if (!dathang.Update(this._conn))
                {
                    transaction.Rollback();
                    return 0L;
                }
                string str = "";
                int index = 0;
                index = 0;
                while (index < chitiet.Length)
                {
                    if (chitiet[index].pID != 0L)
                    {
                        if (!chitiet[index].Update(this._conn))
                        {
                            transaction.Rollback();
                            break;
                        }
                    }
                    else
                    {
                        chitiet[index].pID = long.Parse(this._xuli.f_Get_CapID());
                        if (!chitiet[index].Insert(this._conn))
                        {
                            transaction.Rollback();
                            break;
                        }
                    }
                    str = str + chitiet[index].pID + ",";
                    index++;
                }
                if (index == chitiet.Length)
                {
                    transaction.Commit();
                    this._xuli.f_Update_dulieu(string.Concat(new object[] { "delete from ", this._xuli.pSchema, ".", dtoDPhieuDatHangCT.tableName, " where ", dtoDPhieuDatHangCT.columnID, " not in(", str.Trim(new char[] { ',' }), ")  and ", dtoDPhieuDatHangCT.columnSOLUONGDAGIAO, "=0 and ", dtoDPhieuDatHangCT.columnIDPHIEU, "=", dathang.pID }));
                    return dathang.pID;
                }
                transaction.Rollback();
                num2 = 0L;
            }
            catch
            {
                transaction.Rollback();
                num2 = 0L;
            }
            finally
            {
                this._conn.Close();
                this._conn = null;
            }
            return num2;
        }
    }
}

